import datetime
import openpyxl
import os
from openpyxl.chart import (BarChart, Series, Reference)

START_TIME_STR = "上班打卡时间"
END_TIME_STR = "下班打卡时间"
NAME_STR = "姓名"
overtimeRst = {} #结构{'姓名': {'加班时长':'', '上班天数':'' }}

#对目录统计
def getOvertimeRstByFolder(folder):
    for folderName, subFolderNames, fileNames in os.walk(folder):
        for fileName in fileNames:
            print(fileName)
            getOvertimeRstByFile(folderName + "/" + fileName)
    # pprint.pprint(overtimeRst)

#对单个excel文件，统计加班时长
def getOvertimeRstByFile(excelFile):
    print(excelFile)
    wb = openpyxl.load_workbook(excelFile)
    ws = wb.active
    #获取上班时间/下班时间/姓名所在的列
    colIdxStartTime = 0
    colIdxEndTime = 0
    colIdxName = 0
    row1 = ws[1]
    for cell in row1:
        if cell.value==START_TIME_STR:
            colIdxStartTime = cell.col_idx
        if cell.value==END_TIME_STR:
            colIdxEndTime = cell.col_idx
        if cell.value==NAME_STR:
            colIdxName = cell.col_idx
    #通过iter_rows遍历行,从第二行数据开始读取
    rows = ws.iter_rows(min_row=2)
    for row in rows:
        nameValue = name = row[colIdxName-1].value
        startDatatimeValue = row[colIdxStartTime-1].value
        endDatetimeValue = row[colIdxEndTime-1].value
        if startDatatimeValue is not None and endDatetimeValue is not None :
            # TODO 不明白这里的列索引是从0开始
            #考虑早到
            startDatatime = datetime.datetime.strptime(startDatatimeValue, "%Y-%m-%d %H:%M:%S")
            #不考虑早到
            startDatatimeStr2 = str(startDatatime.year)+"-"+str(startDatatime.month)+"-"+str(startDatatime.day)+" 09:00:00"
            endTimeOver23Str = str(startDatatime.year) + "-" + str(startDatatime.month) + "-" + str(
                startDatatime.day) + " 23:00:00"
            endTimeOver22Str = str(startDatatime.year) + "-" + str(startDatatime.month) + "-" + str(
                startDatatime.day) + " 22:00:00"
            endTimeOver21Str = str(startDatatime.year) + "-" + str(startDatatime.month) + "-" + str(
                startDatatime.day) + " 21:00:00"
            endTimeOver20Str = str(startDatatime.year) + "-" + str(startDatatime.month) + "-" + str(
                startDatatime.day) + " 20:00:00"
            endTimeOver19Str = str(startDatatime.year) + "-" + str(startDatatime.month) + "-" + str(
                startDatatime.day) + " 19:00:00"
            startDatatime2 = datetime.datetime.strptime(startDatatimeStr2, "%Y-%m-%d %H:%M:%S")
            endDatatime = datetime.datetime.strptime(endDatetimeValue, "%Y-%m-%d %H:%M:%S")
            endTimeOver23 = datetime.datetime.strptime(endTimeOver23Str, "%Y-%m-%d %H:%M:%S")
            endTimeOver22 = datetime.datetime.strptime(endTimeOver22Str, "%Y-%m-%d %H:%M:%S")
            endTimeOver21 = datetime.datetime.strptime(endTimeOver21Str, "%Y-%m-%d %H:%M:%S")
            endTimeOver20 = datetime.datetime.strptime(endTimeOver20Str, "%Y-%m-%d %H:%M:%S")
            endTimeOver19 = datetime.datetime.strptime(endTimeOver19Str, "%Y-%m-%d %H:%M:%S")
            #考虑早到
            intervalHoursByEarly = (endDatatime-startDatatime).total_seconds()/3600
            #不考虑早到
            intervalHours = (endDatatime-startDatatime2).total_seconds()/3600
            # 如果小于8小时，有可能是休假或公出，则不做统计
            overTime = 0
            # if name == "王秋霞" and intervalHours >= 7:#因哺乳假的原因,7小时为上班时间
            #     overTime = intervalHours - 7
            # elif intervalHours >= 8:
            #     overTime = intervalHours - 8
            overTime = intervalHours - 8
            overTImeByEarly = intervalHoursByEarly - 8
            overtimeRst.setdefault(name,{})
            overtimeRst[name].setdefault("加班时长", 0)
            overtimeRst[name]["加班时长"]=overtimeRst[name]["加班时长"]+overTime
            overtimeRst[name].setdefault("加班时长(考虑早到)", 0)
            overtimeRst[name]["加班时长(考虑早到)"]=overtimeRst[name]["加班时长(考虑早到)"]+overTImeByEarly
            overtimeRst[name].setdefault("上班天数", 0)
            overtimeRst[name]["上班天数"]=overtimeRst[name]["上班天数"]+1
            overtimeRst[name].setdefault("晚于23点", 0)
            if ((endDatatime - endTimeOver23).total_seconds() > 0):
                overtimeRst[name]["晚于23点"] = overtimeRst[name]["晚于23点"] + 1
            overtimeRst[name].setdefault("晚于22点", 0)
            if ((endDatatime - endTimeOver22).total_seconds() > 0):
                    overtimeRst[name]["晚于22点"] = overtimeRst[name]["晚于22点"] + 1
            overtimeRst[name].setdefault("晚于21点", 0)
            if ((endDatatime-endTimeOver21).total_seconds()>0):
                overtimeRst[name]["晚于21点"] = overtimeRst[name]["晚于21点"] + 1
            overtimeRst[name].setdefault("晚于20点", 0)
            if ((endDatatime-endTimeOver20).total_seconds()>0):
                overtimeRst[name]["晚于20点"] = overtimeRst[name]["晚于20点"] + 1
            overtimeRst[name].setdefault("晚于19点", 0)
            if ((endDatatime-endTimeOver19).total_seconds()>0):
                overtimeRst[name]["晚于19点"] = overtimeRst[name]["晚于19点"] + 1


#把加班数据写到excel中
def showOvertimeRstByExcel():
    wb = openpyxl.Workbook()
    ws = wb.worksheets[0]
    ws['A1'].value = "姓名"
    ws['B1'].value = "总加班时长"
    ws['C1'].value = "总加班时长(考虑早到)"
    ws["D1"].value = "平均加班时长"
    ws["E1"].value = "晚于19点"
    ws["F1"].value = "晚于20点"
    ws["G1"].value = "晚于21点"
    ws["H1"].value = "晚于22点"
    ws["I1"].value = "晚于23点"
    ws["J1"].value = "上班天数"
    rowIdx = 1
    for key in overtimeRst.keys():
        rowIdx += 1
        ws["A"+str(rowIdx)].value = key
        ws["B"+str(rowIdx)].value = round(overtimeRst[key]["加班时长"], 1)
        ws["C"+str(rowIdx)].value = round(overtimeRst[key]["加班时长(考虑早到)"], 1)
        ws["D"+str(rowIdx)].value = round(overtimeRst[key]["加班时长"]/overtimeRst[key]["上班天数"], 1)
        ws["E"+str(rowIdx)].value = overtimeRst[key]["晚于19点"]
        ws["F"+str(rowIdx)].value = overtimeRst[key]["晚于20点"]
        ws["G"+str(rowIdx)].value = overtimeRst[key]["晚于21点"]
        ws["H"+str(rowIdx)].value = overtimeRst[key]["晚于22点"]
        ws["I"+str(rowIdx)].value = overtimeRst[key]["晚于23点"]
        ws["J"+str(rowIdx)].value = overtimeRst[key]["上班天数"]
    wb.save("加班统计.xlsx")
    return wb

#生成图形
def getBarChart(wb):
    ws = wb.active
    maxRow = ws.max_row
    chart1 = BarChart()
    chart1.type = "col"
    chart1.style = 10
    chart1.title = "加班统计"
    chart1.y_axis.title = '加班时长(小时)'
    chart1.x_axis.title = '部门人员'
    data = Reference(ws, min_col=2, min_row=1, max_row=maxRow, max_col=2)
    cats = Reference(ws, min_col=1, min_row=2, max_row=maxRow)
    chart1.add_data(data, titles_from_data=True)
    chart1.set_categories(cats)
    chart1.shape = 4
    ws.add_chart(chart1, "M1")
    wb.save("加班统计.xlsx")

if __name__ == '__main__':
    getOvertimeRstByFolder("/Users/zhangxiangbin/Desktop/kaoqin")
    wb = showOvertimeRstByExcel()
    getBarChart(wb)

